﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyDataService;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace QuanLyBanHang.DAL
{
   public class DalHangHetHan
   {
       public void XoaHang(string MaHang)
       {
          SqlParameter praMaHang=new SqlParameter("MaHang",MaHang);
          dataservice.runQuery("update HangHoa set TrangThai=0 where MaHang ='" + MaHang + "';", praMaHang);
       }
       public DataTable LocHangHoa(string MaLH,string MaNCC,string MaKho,int TruocHan,String NgayTuongLai)
       {
           SqlParameter praMaLH = new SqlParameter("MaLH", MaLH);
           SqlParameter praMaNCC = new SqlParameter("MaNCC", MaNCC);
           SqlParameter praMaKho = new SqlParameter("MaKho", MaKho);
           SqlParameter praTruocHan = new SqlParameter("TruocHan", TruocHan);
           SqlParameter praNgayTuongLai = new SqlParameter("NgayTuongLai", NgayTuongLai);


           StringBuilder SqlBase=new StringBuilder();
           SqlBase.Append("select hanghoa.MaHang,TenHang,convert(varchar(10), NSX, 101) as NSX,convert(varchar(10),HSD, 101) as HSD,cast((HSD-GETDATE()) as int)as SoNgaySD,SoLuong,loaihang.Ten As LoaiHang,NhaCungCap.Ten as NCC,KhoHang.TenKho as TenKho from HangHoa inner join LoaiHang on HangHoa.MaLH=LoaiHang.MaLH inner join NhaCungCap on NhaCungCap.MaNCC=HangHoa.MaNCC inner join KhoHang on KhoHang.MaKho=HangHoa.MaKho where HangHoa.TrangThai=1 ");
           if (!string.IsNullOrEmpty(MaLH))
           {
               SqlBase.Append(" and HangHoa.MaLH='" + MaLH + "' ");
           }
           if (!string.IsNullOrEmpty(MaNCC))
           {
               SqlBase.Append(" and HangHoa.MaNCC='"+MaNCC+"' ");
           }
           if (!string.IsNullOrEmpty(MaKho))
           {
               SqlBase.Append(" and HangHoa.MaKho="+MaKho+" ");
           }
           if (TruocHan!=0)
           {
               SqlBase.Append(" and cast((HSD-GETDATE()) as int) <"+TruocHan+" ");
           }
           if (!string.IsNullOrEmpty(NgayTuongLai))
           {
               SqlBase.Append(" and cast((HSD-convert(varchar(10),N'"+NgayTuongLai+"' , 101)) as int) <"+TruocHan+"  ");
           }

           string sss = SqlBase.ToString();//,praMaLH,praMaKho,praMaNCC,praNgayTuongLai,praTruocHan
           DataTable dataTable = dataservice.selectDatatable(SqlBase.ToString());
           return dataTable;
       }
       public void loadcbo(ComboBox cboTruocNgayHetHan, ComboBox cboNhaCungCap, ComboBox cboKhoHang, ComboBox cboLoaiHang)
       {
           cboNhaCungCap.DataSource = dataservice.selectDatatable("select MaNCC,Ten from NhaCungCap");
           cboNhaCungCap.DisplayMember = "Ten";
           cboNhaCungCap.ValueMember = "MaNCC";
           cboKhoHang.DataSource = dataservice.selectDatatable("select MaKho,TenKho from KhoHang");
           cboKhoHang.DisplayMember = "TenKho";
           cboKhoHang.ValueMember = "MaKho";
           cboLoaiHang.DataSource = dataservice.selectDatatable("select MaLH,Ten from LoaiHang");
           cboLoaiHang.DisplayMember = "Ten";
           cboLoaiHang.ValueMember = "MaLH";
           DataTable dtTruocNgayHetHan=new DataTable();
           dtTruocNgayHetHan.Columns.Add("value");
           dtTruocNgayHetHan.Columns.Add("display");
           dtTruocNgayHetHan.Rows.Add("0", "Đã hết hạn");
           dtTruocNgayHetHan.Rows.Add("15", "Trước 15 ngày");
           dtTruocNgayHetHan.Rows.Add("30", "Trước 30 ngày");
           dtTruocNgayHetHan.Rows.Add("60", "Trước 60 ngày");
           cboTruocNgayHetHan.DataSource = dtTruocNgayHetHan;
           cboTruocNgayHetHan.ValueMember = "value";
           cboTruocNgayHetHan.DisplayMember = "display";
       }
       
   }
}
